﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Text;

namespace DAL
{
    public class RptDAL
    {
        public static float GetTotalSocre(string GUID)
        {
            float fRet = 0.0f;
            if (string.IsNullOrEmpty(GUID))
            {

            }
            else
            {
                DataTable dt = GetDimensionScore(GUID);

            }
            return fRet;
        }

        //答题一致性
        public static bool IsTrust(string GUID)
        {
            bool bRet = false;
            DataTable dt = new DataTable();
            StringBuilder strSql = new StringBuilder();

            strSql.Append(@"SELECT QUESTION_TESTER_SELECTED FROM DBO.TB_TESTER_QUESTION_STATUS
                            WHERE (QUESTION_ID='-1' OR QUESTION_ID='-2') AND 
                            TESTER_SN='").Append(GUID).Append("'");

            dt = DbHelperSQL.Query(strSql.ToString()).Tables[0];

            if (dt == null || dt.Rows.Count != 2)
            {
                bRet = false;
                return bRet;
            }
            if (dt.Rows[0][0].ToString().Equals(dt.Rows[1][0].ToString()))
            {
                bRet = true;
            }
            return bRet;
        }

        //心理健康评语
        public static string GetXljkLevelAndComments(string GUID)
        {
            DataTable dt = new DataTable();
            StringBuilder strSql = new StringBuilder();
            int xljk1_11 = 0;
            int xljk12_17 = 0;
            strSql.Append(@"SELECT SUM(QUESTION_SCORE) AS CHENGXIN1_11 FROM(
            SELECT DISTINCT A.QUESTION_ID,
			B.QUESTION_NO,
            (CASE A.QUESTION_TESTER_SELECTED
            WHEN ('A') THEN (SELECT QUESTION_ANSWER_A_SCORE FROM TB_QUESTION WHERE QUESTION_ID=A.QUESTION_ID)
            WHEN ('B') THEN (SELECT QUESTION_ANSWER_B_SCORE FROM TB_QUESTION WHERE QUESTION_ID=A.QUESTION_ID)
            WHEN ('C') THEN (SELECT QUESTION_ANSWER_C_SCORE FROM TB_QUESTION WHERE QUESTION_ID=A.QUESTION_ID)
            WHEN ('D') THEN (SELECT QUESTION_ANSWER_D_SCORE FROM TB_QUESTION WHERE QUESTION_ID=A.QUESTION_ID)
            WHEN ('E') THEN (SELECT QUESTION_ANSWER_E_SCORE FROM TB_QUESTION WHERE QUESTION_ID=A.QUESTION_ID)
            ELSE '0' END
            ) AS 'QUESTION_SCORE', B.QUESTION_DIMENSIONID
            FROM DBO.TB_TESTER_QUESTION_STATUS A, DBO.TB_QUESTION B
            WHERE A.QUESTION_ID = B.QUESTION_ID AND A.TESTER_SN='").Append(GUID).Append("' ").Append(@"AND (B.QUESTION_NO>=1 AND B.QUESTION_NO<=11)
            AND (SELECT dimension_name FROM dbo.tb_Dimension WHERE dimension_id = B.QUESTION_DIMENSIONID) ='心理健康度') AS T");
            dt = DbHelperSQL.Query(strSql.ToString()).Tables[0];
            if (dt == null || dt.Rows.Count != 1)
            {
                return string.Empty;
            }

            try
            {
                xljk1_11 = Int32.Parse(dt.Rows[0][0].ToString());
            }
            catch
            {
                xljk1_11 = 0;
            }

            strSql.Clear();
            strSql.Append(@"SELECT SUM(QUESTION_SCORE) AS CHENGXIN12_17 FROM(
            SELECT DISTINCT A.QUESTION_ID,
			B.QUESTION_NO,
            (CASE A.QUESTION_TESTER_SELECTED
            WHEN ('A') THEN (SELECT QUESTION_ANSWER_A_SCORE FROM TB_QUESTION WHERE QUESTION_ID=A.QUESTION_ID)
            WHEN ('B') THEN (SELECT QUESTION_ANSWER_B_SCORE FROM TB_QUESTION WHERE QUESTION_ID=A.QUESTION_ID)
            WHEN ('C') THEN (SELECT QUESTION_ANSWER_C_SCORE FROM TB_QUESTION WHERE QUESTION_ID=A.QUESTION_ID)
            WHEN ('D') THEN (SELECT QUESTION_ANSWER_D_SCORE FROM TB_QUESTION WHERE QUESTION_ID=A.QUESTION_ID)
            WHEN ('E') THEN (SELECT QUESTION_ANSWER_E_SCORE FROM TB_QUESTION WHERE QUESTION_ID=A.QUESTION_ID)
            ELSE '0' END
            ) AS 'QUESTION_SCORE', B.QUESTION_DIMENSIONID
            FROM DBO.TB_TESTER_QUESTION_STATUS A, DBO.TB_QUESTION B
            WHERE A.QUESTION_ID = B.QUESTION_ID AND A.TESTER_SN='").Append(GUID).Append("' ").Append(@"AND (B.QUESTION_NO>=12 AND B.QUESTION_NO<=17)
            AND (SELECT dimension_name FROM dbo.tb_Dimension WHERE dimension_id = B.QUESTION_DIMENSIONID) ='心理健康度') AS T");
            dt = DbHelperSQL.Query(strSql.ToString()).Tables[0];
            if (dt == null || dt.Rows.Count != 1)
            {
                return string.Empty;
            }

            try
            {
                xljk12_17 = Int32.Parse(dt.Rows[0][0].ToString());
            }
            catch
            {
                xljk12_17 = 0;
            }

            //心理健康库等级评语判断
            if (xljk1_11 <= 4 && xljk1_11 >= 0)
            {
                if (xljk12_17 <= 4 && xljk12_17 >= 0)
                {
                    return "非常健康-心理非常健康，有很好的精神状态投入工作";
                }
                else if (xljk12_17 >= 11)
                {
                    return "一般-心理健康程度一般，需预防精神分裂";
                }
                else if (xljk12_17 > 4 && xljk12_17 < 11)
                {
                    return "良好-心理比较健康，需注意劳逸结合";
                }
                else
                {
                    //System.Diagnostics.Debug.Assert(false);
                    return string.Empty;
                }
            }
            else if (xljk1_11 >= 11)
            {
                if (xljk12_17 <= 4 && xljk12_17 >= 0)
                {
                    return "良好-心理比较健康，需注意劳逸结合";
                }
                else if (xljk12_17 >= 11)
                {
                    return "较差-需要关注自己的心理健康，建议找心理咨询师咨询一下";
                }
                else if (xljk12_17 > 4 && xljk12_17 < 11)
                {
                    return "一般-心理健康程度一般，需预防神经衰弱";
                }
                else
                {
                    //System.Diagnostics.Debug.Assert(false);
                    return string.Empty;
                }
            }
            else if (xljk1_11 > 4 && xljk1_11 < 11)
            {
                if (xljk12_17 <= 4 && xljk12_17 >= 0)
                {
                    return "良好-心理比较健康，需注意劳逸结合";
                }
                else if (xljk12_17 >= 11)
                {
                    return "一般-心理健康程度一般，需预防精神分裂";
                }
                else if (xljk12_17 > 4 && xljk12_17 < 11)
                {
                    return "良好-心理比较健康，需注意劳逸结合";
                }
                else
                {
                    //System.Diagnostics.Debug.Assert(false);
                    return string.Empty;
                }
            }
            else
            {
                //System.Diagnostics.Debug.Assert(false);
                return string.Empty;
            }
        }

        public static float GetDimWeightInActivity(int dimID, int actID)
        {
            DataTable dt = new DataTable();
            StringBuilder strSql = new StringBuilder();
            strSql.Append(@"select dimension_weight from dbo.tb_Activity_Dimension
                                where activity_id='");
            strSql.Append(actID.ToString());
            strSql.Append("'");
            strSql.Append(" and dimension_id='");
            strSql.Append(dimID.ToString());
            strSql.Append("'");

            dt = DbHelperSQL.Query(strSql.ToString()).Tables[0];
            if (dt == null || dt.Rows.Count != 1)
            {
                return 0.0f;
            }
            else
            {
                return float.Parse(dt.Rows[0][0].ToString());
            }
        }

        public static DataTable GetDimensionScore(string GUID)
        {
            if (string.IsNullOrEmpty(GUID))
            {
                return null;
            }
            else
            {
                DataTable dt = new DataTable();
                StringBuilder strSql = new StringBuilder();
                strSql.Append(@"SELECT QUESTION_DIMENSIONID AS DIMID,
                (SELECT DIMENSION_NAME FROM DBO.TB_DIMENSION WHERE DIMENSION_ID = QUESTION_DIMENSIONID) AS DIMNM,
                cast(SUM(QUESTION_SCORE) as float) AS DIMSCORE,
                (SELECT DIMENSION_68 FROM DBO.TB_DIMENSION WHERE DIMENSION_ID = QUESTION_DIMENSIONID) AS DIMRANGE_START,
                (SELECT DIMENSION_92 FROM DBO.TB_DIMENSION WHERE DIMENSION_ID = QUESTION_DIMENSIONID) AS DIMRANGE_END,
                (SELECT DIMENSION_CLASS_ID FROM DBO.TB_DIMENSION WHERE DIMENSION_ID = QUESTION_DIMENSIONID) AS DIMCLASSID,
                (SELECT DIMENSION_CLASS_NM FROM DBO.TB_DIMENSION_CLASS WHERE DIMENSION_CLASS_ID =
                (SELECT DIMENSION_CLASS_ID FROM DBO.TB_DIMENSION WHERE DIMENSION_ID = QUESTION_DIMENSIONID)) AS DIMCLASSNM,
                (SELECT DIMENSION_DESCRIPTION FROM DBO.TB_DIMENSION WHERE DIMENSION_ID = QUESTION_DIMENSIONID) AS DIMDESC,
                ('等级') AS DIMRESULT,
                (SELECT dimension_17 FROM DBO.TB_DIMENSION WHERE DIMENSION_ID = QUESTION_DIMENSIONID) AS DIMRANGE_17,
                (SELECT dimension_68 FROM DBO.TB_DIMENSION WHERE DIMENSION_ID = QUESTION_DIMENSIONID) AS DIMRANGE_68,
                (SELECT dimension_92 FROM DBO.TB_DIMENSION WHERE DIMENSION_ID = QUESTION_DIMENSIONID) AS DIMRANGE_92,
                ('评语') AS DIMCOMMENTS
                FROM(
                SELECT DISTINCT A.QUESTION_ID,
                (CASE A.QUESTION_TESTER_SELECTED
                WHEN ('A') THEN (SELECT QUESTION_ANSWER_A_SCORE FROM TB_QUESTION WHERE QUESTION_ID=A.QUESTION_ID)
                WHEN ('B') THEN (SELECT QUESTION_ANSWER_B_SCORE FROM TB_QUESTION WHERE QUESTION_ID=A.QUESTION_ID)
                WHEN ('C') THEN (SELECT QUESTION_ANSWER_C_SCORE FROM TB_QUESTION WHERE QUESTION_ID=A.QUESTION_ID)
                WHEN ('D') THEN (SELECT QUESTION_ANSWER_D_SCORE FROM TB_QUESTION WHERE QUESTION_ID=A.QUESTION_ID)
                WHEN ('E') THEN (SELECT QUESTION_ANSWER_E_SCORE FROM TB_QUESTION WHERE QUESTION_ID=A.QUESTION_ID)
                ELSE '0' END
                ) AS 'QUESTION_SCORE', B.QUESTION_DIMENSIONID
                FROM DBO.TB_TESTER_QUESTION_STATUS A, DBO.TB_QUESTION B
                WHERE A.QUESTION_ID = B.QUESTION_ID AND A.TESTER_SN='" + GUID + @"') T

                GROUP BY T.QUESTION_DIMENSIONID");
                dt = DbHelperSQL.Query(strSql.ToString()).Tables[0];
                return dt;
            }
        }

        public static DataTable GetDIMtotalTime(string GUID)
        {
            if (string.IsNullOrEmpty(GUID))
            {
                return null;
            }
            else
            {
                DataTable dt = new DataTable();
                StringBuilder strSql = new StringBuilder();
                strSql.Append(@"SELECT QUESTION_DIMENSIONID AS DIMID,
                (SELECT DIMENSION_NAME FROM DBO.TB_DIMENSION WHERE DIMENSION_ID = QUESTION_DIMENSIONID) AS DIMNM,
                (SELECT DIMENSION_TIME_MIN FROM DBO.TB_DIMENSION WHERE DIMENSION_ID = QUESTION_DIMENSIONID) AS DIM_REF_TIME
                FROM(
                SELECT DISTINCT A.QUESTION_ID,B.QUESTION_DIMENSIONID
                FROM DBO.TB_TESTER_QUESTION_STATUS A, DBO.TB_QUESTION B
                WHERE A.QUESTION_ID = B.QUESTION_ID AND A.TESTER_SN='" + GUID + @"') T
                GROUP BY T.QUESTION_DIMENSIONID");
                dt = DbHelperSQL.Query(strSql.ToString()).Tables[0];
                return dt;
            }
        }

        //获取维度下限时间
        public static DataTable GetDIMtotalTime_xiaxian(string GUID)
        {
            if (string.IsNullOrEmpty(GUID))
            {
                return null;
            }
            else
            {
                DataTable dt = new DataTable();
                StringBuilder strSql = new StringBuilder();
                strSql.Append(@"SELECT QUESTION_DIMENSIONID AS DIMID,
                (SELECT DIMENSION_NAME FROM DBO.TB_DIMENSION WHERE DIMENSION_ID = QUESTION_DIMENSIONID) AS DIMNM,
                (SELECT DIMENSION_TIME_XIAXIAN FROM DBO.TB_DIMENSION WHERE DIMENSION_ID = QUESTION_DIMENSIONID) AS DIM_REF_TIME_XIAXIAN
                FROM(
                SELECT DISTINCT A.QUESTION_ID,B.QUESTION_DIMENSIONID
                FROM DBO.TB_TESTER_QUESTION_STATUS A, DBO.TB_QUESTION B
                WHERE A.QUESTION_ID = B.QUESTION_ID AND A.TESTER_SN='" + GUID + @"') T
                GROUP BY T.QUESTION_DIMENSIONID");
                dt = DbHelperSQL.Query(strSql.ToString()).Tables[0];
                return dt;
            }
        }

        //获取维度上限时间
        public static DataTable GetDIMtotalTime_shangxian(string GUID)
        {
            if (string.IsNullOrEmpty(GUID))
            {
                return null;
            }
            else
            {
                DataTable dt = new DataTable();
                StringBuilder strSql = new StringBuilder();
                strSql.Append(@"SELECT QUESTION_DIMENSIONID AS DIMID,
                (SELECT DIMENSION_NAME FROM DBO.TB_DIMENSION WHERE DIMENSION_ID = QUESTION_DIMENSIONID) AS DIMNM,
                (SELECT DIMENSION_TIME_SHANGXIAN FROM DBO.TB_DIMENSION WHERE DIMENSION_ID = QUESTION_DIMENSIONID) AS DIM_REF_TIME_SHANGXIAN
                FROM(
                SELECT DISTINCT A.QUESTION_ID,B.QUESTION_DIMENSIONID
                FROM DBO.TB_TESTER_QUESTION_STATUS A, DBO.TB_QUESTION B
                WHERE A.QUESTION_ID = B.QUESTION_ID AND A.TESTER_SN='" + GUID + @"') T
                GROUP BY T.QUESTION_DIMENSIONID");
                dt = DbHelperSQL.Query(strSql.ToString()).Tables[0];
                return dt;
            }
        }

        public static DataTable GetTesterInfo(string GUID)
        {
            if (string.IsNullOrEmpty(GUID))
            {
                return null;
            }
            else
            {
                DataTable dt = new DataTable();
                StringBuilder strSql = new StringBuilder();
                strSql.Append(@"SELECT 
                                TESTER_SN AS TESTERGUID,
                                TESTER_NAME AS TESTERNAME,
                                TESTER_TESTSTARTTIME AS DT_START,
                                TESTER_TESTENDTIME AS DT_END,
                                (SELECT POSITION_NAME FROM DBO.TB_POSITION WHERE POSITION_ID=
                                (SELECT ACTIVITY_POSITIONID FROM DBO.TB_ACTIVITY WHERE ACTIVITY_ID=
                                (SELECT ACTIVITY_ID FROM DBO.TB_GUID_NUMBER WHERE GUID_STR = TESTER_SN)))AS TESTERPOSITION,
                                TESTER_TESTSTARTTIME AS TESTERDATE,
                                TESTER_MATCHING AS TESTERTOTALSCORE,
                                TESTER_CONSISTENCYSCORE AS TESTERTRUSTFUL,
                                (SELECT users_incname FROM DBO.TB_ENTERPRISEUSERS WHERE USERS_ID=
                                (SELECT ACTIVITY_ENTERPRISEUID FROM DBO.TB_ACTIVITY WHERE ACTIVITY_ID=
                                (SELECT ACTIVITY_ID FROM DBO.TB_GUID_NUMBER WHERE GUID_STR = TESTER_SN))) AS TESTERCOMPANYNM,
                                tester_dealer
                                FROM DBO.TB_TESTER_INFO
                                WHERE TESTER_SN='" + GUID + "'");
                dt = DbHelperSQL.Query(strSql.ToString()).Tables[0];
                return dt;
            }
        }
    }
}
